﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Data.Odbc
Imports System.Windows.Forms
Imports System.Windows.Forms.Form

Public Class Connection

    ''' <summary>
    ''' Class for Database connection, methods and functions. Supports MSSQL and MySQL. 
    ''' </summary>
    ''' MySQL uses MySQL Connector v1.1
    ''' MSSQL uses SQL connection
    ''' <remarks>
    ''' Author: Dave Villanueva
    ''' Property of CentrIX Information System Inc.
    ''' </remarks>

#Region "Variables"
    Enum ServerType
        MSSQL
        MySQL
    End Enum

    Enum ObjectMethod
        Cleardata
        ObjectLock
    End Enum

    Enum eMode
        AddNew
        Edit
        Normal
    End Enum

    Enum eAction
        NewRec
        EditRec
        RetrieveRec
        IdleRec
    End Enum

    'Local Variables
    Private _DatabaseServer As String = Nothing
    Private _DatabaseName As String = Nothing
    Private _DatabaseUserName As String = Nothing
    Private _DatabasePassword As String = Nothing
    Private _DatabaseServerType As ServerType = ServerType.MSSQL
    Private _IsConnected As Boolean = False
    Private _DataSet As New DataSet
    Private _sSQL As String
 
    Private sqlDA_ As SqlDataAdapter
    Private sqlCN_ As New SqlConnection
    Private sqlCNSec_ As New SqlConnection
    Private sqlCNSet_ As New SqlConnection
    Private sqlCNTran_ As New SqlConnection

    'For Recursive Procedure/Function
    Public lKillRecursive As Boolean

    'ODBC Variables
    Private sqlCon As New SqlConnection
    'Private sqlDA As SqlDataAdapter
    Private sqlReader As SqlDataReader
    Private sqlTran As SqlTransaction

    'MySQL Variables
    Private mysqlCon As New System.Data.Odbc.OdbcConnection
    Private mysqlDA As System.Data.Odbc.OdbcDataAdapter

    'Events
    Public Event Connected()
    Public Event Disconnected()

    'Constant
    Private Const ErrMsg = "Query execution requires an open and available connection to your database server. Please check your provided settings and try again."
    Private Const ErrConnection = "Unable to connect to your database server using the provided settings. Please enter a valid connection settings and try again."

    Public Const TitleHeader = "Simple Solutions ERP"
    Public Const SaveMsg = "Record saved!"
    Public Const WaitSavingMsg = "Saving Record..."
    Public Const WaitLoadingMsg = "Loading Records..."
    Public Const WaitLoginMsg = "Logging in..."

    Public LW As Loading

    Dim oWait As New frmWait

#End Region

#Region "Properties"

    Public Property sqlDA() As SqlDataAdapter
        Get
            sqlDA = sqlDA_
        End Get
        Set(ByVal value As SqlDataAdapter)
            sqlDA_ = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or set the SqlConnection
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property SQLCn() As SqlConnection
        Get
            SQLCn = sqlCN_
        End Get
        Set(ByVal value As SqlConnection)
            sqlCN_ = value
        End Set
    End Property


    Public Property SQLCnSec() As SqlConnection
        Get
            SQLCnSec = sqlCNSec_
        End Get
        Set(ByVal value As SqlConnection)
            sqlCNSec_ = value
        End Set
    End Property


    Public Property SQLCnSet() As SqlConnection
        Get
            SQLCnSet = sqlCNSet_
        End Get
        Set(ByVal value As SqlConnection)
            sqlCNSet_ = value
        End Set
    End Property

    Public Property SQLCnTran() As SqlConnection
        Get
            SQLCnTran = sqlCNTran_
        End Get
        Set(ByVal value As SqlConnection)
            sqlCNTran_ = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the hostname or IP address of the database server.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabaseServer() As String
        Get
            Return _DatabaseServer
        End Get
        Set(ByVal value As String)
            _DatabaseServer = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the database name.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabaseName() As String
        Get
            Return _DatabaseName
        End Get
        Set(ByVal value As String)
            _DatabaseName = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the username to use on connecting the database server.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabaseUserName() As String
        Get
            Return _DatabaseUserName
        End Get
        Set(ByVal value As String)
            _DatabaseUserName = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the password for the username entered.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabasePassword() As String
        Get
            Return _DatabasePassword
        End Get
        Set(ByVal value As String)
            _DatabasePassword = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the type of Database Management System. Default is MSSQL.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabaseServerType() As ServerType
        Get
            Return _DatabaseServerType
        End Get
        Set(ByVal value As ServerType)
            _DatabaseServerType = value
        End Set
    End Property

    ''' <summary>
    ''' Returns a boolean value indicating whether the provided connection settings is connected to the database server.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public ReadOnly Property IsConnected() As Boolean
        Get
            Return _IsConnected
        End Get
    End Property

    ''' <summary>
    ''' Global variable for SQL Script
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property sSQL() As String
        Get
            sSQL = _sSQL
        End Get
        Set(ByVal value As String)
            _sSQL = value
        End Set
    End Property

    ''' <summary>
    ''' Global Settings for Server Address
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property ConnectionServer() As String
        Get
            Return My.Settings._ConnectionServer
        End Get
        Set(ByVal value As String)
            My.Settings._ConnectionServer = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Global Settings for Connection Password
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property ConnectionPassword() As String
        Get
            Return My.Settings._ConnectionPassword
        End Get
        Set(ByVal value As String)
            My.Settings._ConnectionPassword = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Global Settings for Database Connection Username
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property ConnectionUserName() As String
        Get
            Return My.Settings._ConnectionUserName
        End Get
        Set(ByVal value As String)
            My.Settings._ConnectionUserName = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Global Settings for Security Database Name
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property SecDatabaseName() As String
        Get
            Return My.Settings._SecDatabaseName
        End Get
        Set(ByVal value As String)
            My.Settings._SecDatabaseName = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Global Settings for Settings Database Name
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property SetDatabaseName() As String
        Get
            Return My.Settings._SetDatabaseName
        End Get
        Set(ByVal value As String)
            My.Settings._SetDatabaseName = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Global Settings for Transaction Database Name
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property TranDatabaseName() As String
        Get
            Return My.Settings._TranDatabaseName
        End Get
        Set(ByVal value As String)
            My.Settings._TranDatabaseName = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Current UserName
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property CurrentUserName() As String
        Get
            Return My.Settings.oUserName
        End Get
        Set(ByVal value As String)
            My.Settings.oUserName = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Current FullName
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property CurrentFullName() As String
        Get
            Return My.Settings.oFullName
        End Get
        Set(ByVal value As String)
            My.Settings.oFullName = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Current Role
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property CurrentRole() As String
        Get
            Return My.Settings.oUserRole
        End Get
        Set(ByVal value As String)
            My.Settings.oUserRole = value
            My.Settings.Save()
        End Set
    End Property

    ''' <summary>
    ''' Current Company
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property CurrentCompany() As String
        Get
            Return My.Settings.oCompany
        End Get
        Set(ByVal value As String)
            My.Settings.oCompany = value
            My.Settings.Save()
        End Set
    End Property

#End Region

#Region "Constructor"

    ''' <summary>
    ''' Create a new instance of database connection class.
    ''' </summary>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Sub New()
        'default constructor
    End Sub

    ''' <summary>
    ''' Create a new instance of database connection class providing the connection settings.
    ''' </summary>
    ''' <param name="Host">The hostname or IP address of the database server.</param>
    ''' <param name="Database">The database name.</param>
    ''' <param name="UserName">The username to use to connect to the database server.</param>
    ''' <param name="Password">The password for the username entered.</param>
    ''' <param name="ServerType">The type of Database Management System.</param>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Sub New(ByVal Host As String, ByVal Database As String, ByVal UserName As String, ByVal Password As String, ByVal ServerType As ServerType)
        _DatabaseServer = Host
        _DatabaseName = Database
        _DatabaseUserName = UserName
        _DatabasePassword = Password
        _DatabaseServerType = ServerType

    End Sub

#End Region

#Region "Procedures"

    ''' <summary>
    ''' Bind objects to the current DataTable
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <param name="dTable">Name of the DataTable</param>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Function Databind(ByVal oCollection As Object, ByVal dTable As Object) As Boolean
        Dim obj As Object
        DataUnbind(oCollection)
        For Each obj In oCollection.Controls
            If Mid$(obj.tag, 1, 1) = "!" Then
                If TypeName(obj) = "TextBoxX" Then
                    obj.DataBindings.Add("text", dTable, obj.name)
                ElseIf TypeName(obj) = "DateTimeInput" Then
                    obj.DataBindings.Add("text", dTable, obj.name)
                ElseIf TypeName(obj) = "LabelX" Then
                    obj.DataBindings.Add("text", dTable, obj.name)
                ElseIf TypeName(obj) = "CheckBoxX" Then
                    obj.DataBindings.Add("Checked", dTable, obj.name)
                ElseIf TypeName(obj) = "DoubleInput" Then
                    obj.DataBindings.Add("text", dTable, obj.name)
                ElseIf TypeName(obj) = "PictureBox" Then
                    obj.DataBindings.Add("Image", dTable, obj.name, True, _
                            Windows.Forms.DataSourceUpdateMode.OnPropertyChanged)
                ElseIf TypeName(obj) = "DataGridViewX" Then
                    obj.datasource = dTable
                    obj.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                    obj.AllowUserToAddRows = False
                    obj.EditMode = DataGridViewEditMode.EditProgrammatically
                    obj.MultiSelect = False
                ElseIf TypeName(obj) = "BindingNavigator" Then
                    obj.BindingSource = dTable
                ElseIf IsContainer(obj) Then
                    Databind(obj, dTable)
                End If
            End If
        Next
        Return True
    End Function

    Public Sub DataGridBind(ByVal DataGrid As Object, ByVal dTable As Object)
        DataGrid.datasource = dTable
        DataGrid.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        DataGrid.AllowUserToAddRows = False
        DataGrid.MultiSelect = False
    End Sub

    Public Sub GetChild(ByVal sCriteria As String, ByVal pBindingSource As BindingSource)
        pBindingSource.Filter = sCriteria
    End Sub

    ''' <summary>
    ''' Unbind objects to the current DataTable
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Function DataUnbind(ByVal oCollection As Object) As Boolean
        Dim obj As Object
        For Each obj In oCollection.Controls
            If Mid$(obj.tag, 1, 1) = "!" Then
                If TypeName(obj) = "TextBoxX" Then
                    obj.DataBindings.Clear()
                ElseIf TypeName(obj) = "DateTimeInput" Then
                    obj.DataBindings.Clear()
                ElseIf TypeName(obj) = "CheckBoxX" Then
                    obj.DataBindings.Clear()
                ElseIf TypeName(obj) = "DoubleInput" Then
                    obj.DataBindings.Clear()
                ElseIf TypeName(obj) = "PictureBox" Then
                    obj.DataBindings.Clear()
                ElseIf TypeName(obj) = "DataGridViewX" Then
                    obj.DataSource = Nothing
                    obj.EditMode = DataGridViewEditMode.EditProgrammatically
                ElseIf TypeName(obj) = "BindingSource" Then
                    obj.DataSource = Nothing
                ElseIf TypeName(obj) = "BindingNavigator" Then
                    obj.BindingSource = Nothing
                ElseIf IsContainer(obj) Then
                    DataUnbind(obj)
                End If
            End If
        Next
        Return False
    End Function

    ''' <summary>
    ''' Controls the objects in the collection
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <param name="oMethod">Either Clear the data or Lock/Unlock the objects</param>
    ''' <param name="lLock">Option when the method is Lock/Unlock</param>
    ''' <remarks></remarks>
    Public Sub ObjectControl(ByVal oCollection As Object, ByVal oMethod As ObjectMethod, Optional ByVal lLock As Boolean = False, Optional ByVal lExceptDTG As Boolean = False)
        Dim obj As Object
        If oMethod = ObjectMethod.Cleardata Then
            If lExceptDTG = True Then
                For Each obj In oCollection.Controls
                    If TypeName(obj) = "TextBoxX" Then
                        obj.Text = ""
                    ElseIf IsContainer(obj) Then
                        ObjectControl(obj, oMethod, lLock, lExceptDTG)
                    End If
                Next
            Else
                For Each obj In oCollection.Controls
                    If TypeName(obj) = "TextBoxX" Then
                        obj.Text = ""
                    ElseIf TypeName(obj) = "DateTimeInput" Then
                        obj.Text = ""
                    ElseIf TypeName(obj) = "CheckBoxX" Then
                        obj.checked = False
                    ElseIf TypeName(obj) = "ComboBoxEX" Then
                        obj.Items.Clear()
                    ElseIf TypeName(obj) = "DoubleInput" Then
                        obj.Text = "0.0"
                    ElseIf TypeName(obj) = "PictureBox" Then
                        obj.image = Nothing
                    ElseIf TypeName(obj) = "DataGridViewX" Then
                        obj.Columns.Clear()
                    ElseIf IsContainer(obj) Then
                        ObjectControl(obj, oMethod, lLock, lExceptDTG)
                    End If
                Next
            End If

        ElseIf oMethod = ObjectMethod.ObjectLock Then
            For Each obj In oCollection.Controls
                If TypeName(obj) = "TextBoxX" Then
                    obj.Enabled = IIf(Mid$(obj.tag, 3, 1) = "!", True, Not lLock)
                ElseIf TypeName(obj) = "DateTimeInput" Then
                    obj.Enabled = IIf(Mid$(obj.tag, 3, 1) = "!", True, Not lLock)
                ElseIf TypeName(obj) = "CheckBoxX" Then
                    obj.Enabled = IIf(Mid$(obj.tag, 3, 1) = "!", True, Not lLock)
                ElseIf TypeName(obj) = "DoubleInput" Then
                    obj.Enabled = IIf(Mid$(obj.tag, 3, 1) = "!", True, Not lLock)
                ElseIf TypeName(obj) = "ComboBoxEX" Then
                    obj.Enabled = IIf(Mid$(obj.tag, 3, 1) = "!", True, Not lLock)
                ElseIf TypeName(obj) = "DataGridViewX" Then
                    obj.EditMode = IIf(lLock = True, DataGridViewEditMode.EditProgrammatically, DataGridViewEditMode.EditOnKeystroke)
                ElseIf TypeName(obj) = "ButtonX" Then
                    obj.Enabled = IIf(Mid$(obj.tag, 3, 1) = "!", True, Not lLock)
                ElseIf IsContainer(obj) Then
                    ObjectControl(obj, oMethod, lLock, lExceptDTG)
                End If
            Next

        End If

        ObjectEditing(oCollection, lLock)

    End Sub

    Public Sub ObjectEditing(ByVal oCollection As Object, ByVal lLock As Boolean)
        Dim obj As Object
        For Each obj In oCollection.Controls
            If TypeName(obj) = "TextBoxX" Then
                obj.ReadOnly = lLock
            ElseIf IsContainer(obj) Then
                ObjectEditing(obj, lLock)
            End If
        Next
    End Sub

    ''' <summary>
    ''' Populate ComboBox with the result of the sql query
    ''' </summary>
    ''' <param name="obj"></param>
    ''' <param name="FieldName">Returning field of the table </param>
    ''' <param name="TableName">Name of the table</param>
    ''' <param name="Condition">Condition of the query</param>
    ''' <remarks></remarks>
    Public Sub LoadComboValues(ByVal obj As Object, ByVal FieldName As String, ByVal TableName As String, Optional ByVal Condition As String = "")
        Dim sqlDS As DataSet = New DataSet
        Dim sqlDataAdapter As New SqlDataAdapter
        Try
            sSQL = "select " & FieldName & " from " & TableName & IIf(Condition = "", "", " where " & Condition)
            sqlDataAdapter.SelectCommand = New SqlCommand(sSQL, SQLCn)
            sqlDataAdapter.Fill(sqlDS, TableName)
            obj.Text = ""
            obj.Items.Clear()
            For i As Integer = 0 To sqlDS.Tables(TableName).Rows.Count - 1
                obj.items.add(sqlDS.Tables(0).Rows(i).Item(0))
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' Connect to the database server using the provided settings.
    ''' </summary>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Function Connect(ByRef conn As SqlConnection) As Boolean
        Dim constr As String
        constr = "Data Source=" & DatabaseServer & ";Database=" & _
                DatabaseName & ";UID=" & DatabaseUserName & ";PWD=" & DatabasePassword
        Try
            If conn.State = ConnectionState.Closed Then
                conn = New SqlConnection(constr)
                conn.Open()
            End If
            Return True
        Catch e As SqlException
            MsgBox("Error: " & e.ToString)
            Return False
            'conn.Close()
        End Try
    End Function

    ''' <summary>
    ''' Open a record to view
    ''' </summary>
    ''' <param name="sField"></param>
    ''' <param name="sTable"></param>
    ''' <param name="sqlDataSet"></param>
    ''' <param name="conn"></param>
    ''' <remarks></remarks>
    Public Sub OpenRecord_(ByVal sField As String, ByVal sTable As String, _
             ByRef sqlDataSet As DataSet, ByVal conn As SqlConnection, _
             Optional ByRef sqlAdapter As SqlDataAdapter = Nothing, _
             Optional ByVal sCondition As String = "")

        If sqlAdapter Is Nothing Then
            Dim sqlDataAdapter As New SqlDataAdapter
            sSQL = "select " & sField & " from " & sTable & "" & IIf(sCondition = "", "", " where " & sCondition)
            sqlDataAdapter.SelectCommand = New SqlCommand(sSQL, conn)
            sqlDataAdapter.Fill(sqlDataSet, sTable)
        Else
            sSQL = "select " & sField & " from " & sTable & "" & IIf(sCondition = "", "", " where " & sCondition)
            sqlAdapter.SelectCommand = New SqlCommand(sSQL, conn)
            sqlAdapter.Fill(sqlDataSet, sTable)
        End If

    End Sub

    Public Function OpenRecord(ByVal conn As SqlConnection, _
                           ByRef sAdapter As SqlDataAdapter, _
                           ByRef sDs As DataSet, _
                           ByVal cField As String, ByVal cTable As String, _
                           Optional ByVal cCondition As String = "") As String
        Try
            Dim sCommand As SqlCommand
            Dim sBuilder As SqlCommandBuilder
            Dim sql As String = "SELECT " & cField & " FROM " & cTable & IIf(cCondition = "", "", " WHERE " & cCondition)

            sCommand = New SqlCommand(sql, conn)
            sAdapter = New SqlDataAdapter(sCommand)
            sBuilder = New SqlCommandBuilder(sAdapter)
            sAdapter.Fill(sDs, cTable)
            Return ""
        Catch ex As Exception
            Return ex.Message
        End Try
    End Function



    ''' <summary>
    ''' Use to transaction to stored procedure
    ''' </summary>
    ''' <param name="conn"></param>
    ''' <param name="StoredProcedure"></param>
    ''' <param name="oCollection"></param>
    ''' <remarks></remarks>
    Public Sub ExecuteStoredProc(ByVal conn As SqlConnection, ByVal StoredProcedure As String, _
        ByVal oCollection As Object, ByVal oAction As eAction)
        Try
            Dim myCommand As SqlCommand = New SqlCommand(StoredProcedure, conn)
            myCommand.CommandType = CommandType.StoredProcedure
            FillParameters(oCollection, myCommand)
            myCommand.Parameters.Add("@lAction", SqlDbType.Int).Value = oAction
            myCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub ExecuteNonQuery(ByVal conn As SqlConnection, ByVal sSQL As String)
        Try
            Dim myCommand As SqlCommand = New SqlCommand(sSQL, conn)
            myCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' Execute SQL script
    ''' </summary>
    ''' <param name="oSQL">SQL Script</param>
    ''' <param name="sqlDataSet">Dataset return</param>
    ''' <remarks></remarks>
    Public Sub ExecuteQuery(ByVal oSQL As String, ByRef sqlDataSet As DataSet)
        Try
            Dim sqlDataAdapter As New SqlDataAdapter
            sqlDataAdapter.SelectCommand = New SqlCommand(oSQL, SQLCn)
            sqlDataAdapter.Fill(sqlDataSet)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, TitleHeader)
        End Try
    End Sub

    Public Sub ExecuteQuery(ByVal oSQL As String, ByRef sqlDataSet As DataSet, ByVal sqlCon As SqlConnection)
        Try
            Dim sqlDataAdapter As New SqlDataAdapter
            sqlDataAdapter.SelectCommand = New SqlCommand(oSQL, sqlCon)
            sqlDataAdapter.Fill(sqlDataSet)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, TitleHeader)
        End Try
    End Sub

    Public Function GetCurrentID(ByVal cTableName As String) As Decimal
        Dim tmpDSet As New DataSet
        ExecuteQuery("exec spGetCurrentID '" & cTableName & "'", tmpDSet)
        Return CDec(tmpDSet.Tables(0).Rows(0).Item(0).ToString)
    End Function


    ''' <summary>
    ''' Use to fill up parameters to transact stored procedure
    ''' </summary>
    ''' <param name="oCollection"></param>
    ''' <param name="sqlCmd"></param>
    ''' <remarks></remarks>
    Private Sub FillParameters(ByVal oCollection As Object, ByVal sqlCmd As SqlCommand)
        Try
            Dim obj As Object
            For Each obj In oCollection.Controls
                If Mid$(obj.tag, 2, 1) = "!" Then
                    If TypeName(obj) = "TextBoxX" Then
                        sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.VarChar).Value = obj.text
                    ElseIf TypeName(obj) = "DateTimeInput" Then
                        sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.Date).Value = obj.text
                    ElseIf TypeName(obj) = "CheckBoxX" Then
                        sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.Bit).Value = obj.checked
                    ElseIf TypeName(obj) = "DoubleInput" Then
                        sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.VarChar).Value = obj.value
                    ElseIf TypeName(obj) = "ComboBoxEX" Then
                        sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.VarChar).Value = obj.text
                    ElseIf TypeName(obj) = "PictureBox" Then
                        Dim picByte As Byte() = ImageToByte(obj.image)
                        sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.Image).Value = picByte
                    ElseIf IsContainer(obj) Then
                        FillParameters(obj, sqlCmd)
                    End If
                End If
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Function ImageToByte(ByVal img As System.Drawing.Image) As Byte()
        Dim imgStream As MemoryStream = New MemoryStream()

        img.Save(imgStream, System.Drawing.Imaging.ImageFormat.Png)

        imgStream.Close()
        Dim byteArray As Byte() = imgStream.ToArray()
        imgStream.Dispose()

        Return byteArray
    End Function

    Public Function GetImage(ByVal obj As Object) As System.Drawing.Image
        Try
            Dim memorystream As System.IO.MemoryStream
            Dim ImageByte As Byte()

            If TypeOf obj Is System.DBNull Then
                Return Nothing
            Else
                ImageByte = obj
                memorystream = New IO.MemoryStream(ImageByte)

                If ImageByte.Length = 0 Then
                    Return Nothing
                Else
                    Return System.Drawing.Image.FromStream(memorystream)
                End If
                memorystream.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "NavSystemStructure") : Return Nothing
        End Try

    End Function

    Public Sub WaitWindow(ByVal sMsg As String, ByVal lShow As Boolean, Optional ByVal lSplash As Boolean = False)
        Dim loc As Drawing.Point
        loc = New Drawing.Point(0, 0)

        If lSplash = True Then
            Dim SS As New SplashScreen
            If SS.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Exit Sub
            End If
        Else
            If lShow = True Then
                LW = New Loading(loc)
            Else
                LW.Dispose()
            End If

        End If
    End Sub

    Public Function PopupPicker(ByVal nID As Integer, ByVal sqlCon As SqlConnection, _
             Optional ByVal cCond As String = "", Optional ByVal lCompany As Boolean = False) As String
        Dim sSQL As String
        Dim sPicker As New Picker
        Dim sDSet As New DataSet

        ExecuteQuery("select * from Set_Picker where nID = " & nID, sDSet, sqlCon)
        Dim sTitle As String = sDSet.Tables(0).Rows(0).Item(1).ToString
        Dim sField As String = sDSet.Tables(0).Rows(0).Item(2).ToString
        Dim sTableName As String = sDSet.Tables(0).Rows(0).Item(3).ToString
        Dim sCondition As String = sDSet.Tables(0).Rows(0).Item(4).ToString
        Dim sDatabase As String = sDSet.Tables(0).Rows(0).Item(5).ToString

        sSQL = "select " & sField & " from " & sTableName & IIf(cCond = "", "", " where " & cCond) & IIf(cCond = "", (IIf(sCondition = "", "", " where " & sCondition)), (IIf(sCondition = "", "", " and " & sCondition))) & _
        IIf(cCond = "" And sCondition = "", (IIf(lCompany = False, "", " where cCompanyID='" & CurrentCompany & "'")), (IIf(lCompany = False, "", " and cCompanyID='" & CurrentCompany & "'")))
        With sPicker
            .sDatabase = sDatabase
            .sSQL = sSQL
            .sField = sField
            .sCondition = cCond & sCondition
            .sPickerTitle = sTitle
        End With


        If sPicker.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Return sPicker.sReturn
        Else
            Return ""
        End If
    End Function


    Public Sub DatagridPopup(ByVal dtgX As Object, ByVal sConn As SqlConnection, Optional ByVal cTableName As String = "")
        Dim myCommand As New SqlCommand
        Dim sAdapter As New SqlDataAdapter
        Dim sDataSet As New DataSet

        Dim sTableName As String
        sTableName = IIf(cTableName <> "", cTableName, dtgX.name)

        myCommand.Connection = sConn
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.CommandText = "spColumnSchema"
        myCommand.Parameters.Add("@TableName", SqlDbType.VarChar).Value = sTableName

        sAdapter = New SqlDataAdapter(myCommand)
        sAdapter.Fill(sDataSet)

        Dim dgvColTx As System.Windows.Forms.DataGridViewTextBoxColumn
        Dim dgvColCmb As System.Windows.Forms.DataGridViewComboBoxColumn
        Dim dgvColChk As System.Windows.Forms.DataGridViewCheckBoxColumn

        dtgX.DataSource = Nothing
        dtgX.Columns.Clear()
        For i As Integer = 0 To sDataSet.Tables(0).Rows.Count - 1
            If Microsoft.VisualBasic.Right(sDataSet.Tables(0).Rows(i).Item(1), 1) = "●" Then
                dgvColCmb = New System.Windows.Forms.DataGridViewComboBoxColumn
                With dgvColCmb
                    Application.DoEvents()
                    .DataPropertyName = sDataSet.Tables(0).Rows(i).Item(0)
                    .HeaderText = Mid$(sDataSet.Tables(0).Rows(i).Item(1), 1, Len(sDataSet.Tables(0).Rows(i).Item(1)) - 1)
                    .Name = dtgX.name & sDataSet.Tables(0).Rows(i).Item(0)
                    .Visible = IIf(sDataSet.Tables(0).Rows(i).Item(2) = 1, True, False)
                    .Width = IIf(sDataSet.Tables(0).Rows(i).Item(3) >= 50, 250, 100)
                End With
                dtgX.Columns.Add(dgvColCmb)
            ElseIf Microsoft.VisualBasic.Right(sDataSet.Tables(0).Rows(i).Item(1), 1) = "▪" Then
                dgvColChk = New System.Windows.Forms.DataGridViewCheckBoxColumn
                With dgvColChk
                    Application.DoEvents()
                    .DataPropertyName = sDataSet.Tables(0).Rows(i).Item(0)
                    .HeaderText = Mid$(sDataSet.Tables(0).Rows(i).Item(1), 1, Len(sDataSet.Tables(0).Rows(i).Item(1)) - 1)
                    .Name = dtgX.name & sDataSet.Tables(0).Rows(i).Item(0)
                    .Visible = IIf(sDataSet.Tables(0).Rows(i).Item(2) = 1, True, False)
                    .Width = IIf(sDataSet.Tables(0).Rows(i).Item(3) >= 50, 250, 100)
                End With
                dtgX.Columns.Add(dgvColChk)
            Else
                dgvColTx = New System.Windows.Forms.DataGridViewTextBoxColumn
                With dgvColTx
                    Application.DoEvents()
                    .DataPropertyName = sDataSet.Tables(0).Rows(i).Item(0)
                    .HeaderText = sDataSet.Tables(0).Rows(i).Item(1)
                    .Name = dtgX.name & sDataSet.Tables(0).Rows(i).Item(0)
                    .Visible = IIf(sDataSet.Tables(0).Rows(i).Item(2) = 1, True, False)
                    .Width = IIf(sDataSet.Tables(0).Rows(i).Item(3) >= 50, 250, 100)
                End With
                dtgX.Columns.Add(dgvColTx)
            End If
        Next

        


    End Sub

    Public Sub DatagridValues(ByVal sConn As SqlConnection, ByVal DSet As DataSet, ByVal dtgX As Object, _
                              ByVal row As DataRow)
        Dim myCommand As New SqlCommand
        Dim sAdapter As New SqlDataAdapter
        Dim sDataSet As New DataSet

        Dim sTableName As String
        sTableName = dtgX.name

        myCommand.Connection = sConn
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.CommandText = "spColumnSchema"
        myCommand.Parameters.Add("@TableName", SqlDbType.VarChar).Value = sTableName

        sAdapter = New SqlDataAdapter(myCommand)
        sAdapter.Fill(sDataSet)

        For i As Integer = 0 To sDataSet.Tables(0).Rows.Count - 1
            Select Case sDataSet.Tables(0).Rows(i).Item(4)
                Case "numeric", "bit"
                    row(sDataSet.Tables(0).Rows(i).Item(0)) = 0
                Case "varchar"
                    row(sDataSet.Tables(0).Rows(i).Item(0)) = ""
                Case "datetime"
                    row(sDataSet.Tables(0).Rows(i).Item(0)) = Now
            End Select
        Next

    End Sub


    Public Sub PopulateValues(ByVal sConn As SqlConnection, ByVal DSet As DataSet, ByVal sTableName As String, _
                              ByRef row As Object)
        Try
            Dim myCommand As New SqlCommand
            Dim sAdapter As New SqlDataAdapter
            Dim sDataSet As New DataSet

            myCommand.Connection = sConn
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.CommandText = "spColumnSchema"
            myCommand.Parameters.Add("@TableName", SqlDbType.VarChar).Value = sTableName

            sAdapter = New SqlDataAdapter(myCommand)
            sAdapter.Fill(sDataSet)

            For i As Integer = 0 To sDataSet.Tables(0).Rows.Count - 1
                Select Case sDataSet.Tables(0).Rows(i).Item(4)
                    Case "numeric", "bit"
                        row(sDataSet.Tables(0).Rows(i).Item(0)) = 0
                    Case "varchar"
                        row(sDataSet.Tables(0).Rows(i).Item(0)) = ""
                    Case "datetime"
                        row(sDataSet.Tables(0).Rows(i).Item(0)) = Now
                End Select

            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


    End Sub

#End Region

#Region "Functions"

    ''' <summary>
    ''' Execute SQL Statement and return the specified field. Preferably used on getting one record of a certain table.
    ''' </summary>
    ''' <param name="sField">Name of the returning field</param>
    ''' <param name="sTable">Name of the table</param>
    ''' <param name="sCondition">Condition to be applied</param>
    ''' <returns>Return a string</returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Function GetValueFrTable(ByVal sField As String, ByVal sTable As String, ByVal sCondition As String) As String
        Dim sCond As String
        Dim sqlDS As DataSet = New DataSet
        Dim sqlDataAdapter As New SqlDataAdapter

        If (sCondition <> "") Then
            sCond = " WHERE " & sCondition
        Else
            sCond = ""
        End If

        sSQL = "SELECT " & sField & " FROM " & sTable & sCond
        sqlDataAdapter.SelectCommand = New SqlCommand(sSQL, SQLCn)
        sqlDataAdapter.Fill(sqlDS, sTable)

        If sqlDS.Tables(sTable).Rows.Count > 0 Then
            GetValueFrTable = sqlDS.Tables(sTable).Rows(0).Item(0).ToString
        Else
            GetValueFrTable = ""
        End If
    End Function

    ''' <summary>
    ''' Use to determine if the object which was mandatory was filled with data
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <returns>True if ok</returns>
    ''' <remarks></remarks>
    Public Function MandatoryOK(ByVal oCollection As Object) As Boolean
        Dim obj As Object
        Dim tmpReturn As Boolean
        Try
            If lKillRecursive = False Then
                tmpReturn = True
                For Each obj In oCollection.Controls
                    If Mid$(obj.tag, 3, 1) = "!" Then
                        If TypeName(obj) = "TextBoxX" Then
                            If obj.text = "" Then
                                MsgBox("The value of " & obj.name & " is mandatory.", MsgBoxStyle.Exclamation, TitleHeader)
                                tmpReturn = False
                                lKillRecursive = True
                                Exit For
                            End If
                        ElseIf TypeName(obj) = "DateTimeInput" Then
                            If obj.text = "" Then
                                MsgBox("The value of " & obj.name & " is mandatory.", MsgBoxStyle.Exclamation, TitleHeader)
                                tmpReturn = False
                                lKillRecursive = True
                                Exit For
                            End If
                        ElseIf TypeName(obj) = "ComboBoxEX" Then
                            If obj.text = "" Then
                                MsgBox("The value of " & obj.name & " is mandatory.", MsgBoxStyle.Exclamation, TitleHeader)
                                tmpReturn = False
                                lKillRecursive = True
                                Exit For
                            End If
                        ElseIf IsContainer(obj) Then
                            tmpReturn = MandatoryOK(obj)
                        End If
                    End If
                Next
            End If
        Catch ex As Exception
            tmpReturn = False
        End Try
        MandatoryOK = tmpReturn
    End Function

    ''' <summary>
    ''' Get Search String of the set object use to searching
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSearchString(ByVal oCollection As Object, Optional ByVal lAll As Boolean = False) As String
        Dim obj As Object
        Dim tmpResult As String = ""
        Try
            If lAll = True Then
                For Each obj In oCollection.Controls
                    If Mid$(obj.tag, 3, 1) = "!" Then
                        If TypeName(obj) = "TextBoxX" Then
                            If obj.Text <> "" Then
                                tmpResult = tmpResult & IIf(tmpResult = "", obj.name & " like '" & obj.Text & "%'", _
                                                            " and " & obj.name & " like '" & obj.Text & "%'")
                            End If
                        ElseIf TypeName(obj) = "DateTimeInput" Then
                            If obj.Text <> "" Then
                                tmpResult = tmpResult & IIf(tmpResult = "", obj.name & " = '" & obj.Text & "'", _
                                                            " and " & obj.name & " = '" & obj.Text & "'")
                            End If
                        ElseIf TypeName(obj) = "CheckBoxX" Then
                            If obj.CheckState <> "0" Then
                                tmpResult = tmpResult & IIf(tmpResult = "", obj.name & " like '" & obj.CheckState & "%'", _
                                                            " and " & obj.name & " like '" & obj.CheckState & "%'")
                            End If
                        ElseIf TypeName(obj) = "ComboBoxEX" Then
                            If obj.Text <> "" Then
                                tmpResult = tmpResult & IIf(tmpResult = "", obj.name & " like '" & obj.Text & "%'", _
                                                            " and " & obj.name & " like '" & obj.Text & "%'")
                            End If
                        ElseIf IsContainer(obj) Then
                            tmpResult = tmpResult & GetSearchString(obj)
                        End If
                    End If
                Next
            Else
                For Each obj In oCollection.Controls
                    If Mid$(obj.tag, 3, 1) = "!" Then
                        If TypeName(obj) = "TextBoxX" Then
                            If obj.Text <> "" Then
                                tmpResult = tmpResult & IIf(tmpResult = "", obj.name & " like '" & obj.Text & "%'", _
                                                            " and " & obj.name & " like '" & obj.Text & "%'")
                            End If
                        ElseIf TypeName(obj) = "DateTimeInput" Then
                            If obj.Text <> "" Then
                                tmpResult = tmpResult & IIf(tmpResult = "", obj.name & " = '" & obj.Text & "'", _
                                                            " and " & obj.name & " = '" & obj.Text & "'")
                            End If
                        ElseIf TypeName(obj) = "CheckBoxX" Then
                            If obj.Text <> "" Then
                                tmpResult = tmpResult & IIf(tmpResult = "", obj.name & " like '" & obj.CheckState & "%'", _
                                                                " and " & obj.name & " like '" & obj.CheckState & "%'")
                            End If
                        ElseIf TypeName(obj) = "ComboBoxEX" Then
                            If obj.Text <> "" Then
                                tmpResult = tmpResult & IIf(tmpResult = "", obj.name & " like '" & obj.Text & "%'", _
                                                            " and " & obj.name & " like '" & obj.Text & "%'")
                            End If
                        ElseIf IsContainer(obj) Then
                            tmpResult = tmpResult & GetSearchString(obj, lAll)
                        End If
                    End If

                Next
            End If

            GetSearchString = tmpResult
        Catch ex As Exception
            GetSearchString = ""
        End Try
    End Function


    ''' <summary>
    ''' Determine if a control is a container or hold another control 
    ''' </summary>
    ''' <param name="obj">Object variable</param>
    ''' <returns>Boolean</returns>
    ''' <remarks></remarks>
    Public Function IsContainer(ByVal obj As Object) As Boolean
        If obj.controls.count > 0 Then
            Return True
        End If
    End Function


    Public Sub CheckDBExistance(ByVal conn As SqlConnection, _
                                ByVal cDBName As String, _
                                ByVal cBackupPath As String, _
                                ByVal cDataPath As String, _
                                ByVal cLogPath As String)
        Try
            Dim myCommand As SqlCommand = New SqlCommand("spDBDefaults", conn)
            myCommand.CommandType = CommandType.StoredProcedure

            myCommand.Parameters.Add("@cDBName", SqlDbType.VarChar).Value = cDBName
            myCommand.Parameters.Add("@cBackupPath", SqlDbType.VarChar).Value = cBackupPath
            myCommand.Parameters.Add("@cDataPath", SqlDbType.VarChar).Value = cDataPath
            myCommand.Parameters.Add("@cLogPath", SqlDbType.VarChar).Value = cLogPath

            myCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub SetIncrementSeed(ByVal DSet As DataSet, ByVal DGridView As Object, ByVal sField As String)
        DSet.Tables(DGridView.name).Columns(sField).AutoIncrement = True
        DSet.Tables(DGridView.name).Columns(sField).AutoIncrementSeed = GetCurrentID(DGridView.Name) + 1
    End Sub



#End Region

End Class

